package com.quan.project.mapper;

import com.quan.project.entity.Match;
import com.quan.project.vo.MatchVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 比赛对阵Mapper接口
 */
@Mapper
public interface MatchMapper {
    
    /**
     * 插入对阵记录
     */
    int insert(Match match);
    
    /**
     * 批量插入对阵记录
     */
    int insertBatch(@Param("matches") List<Match> matches);
    
    /**
     * 根据ID查询对阵
     */
    Match selectById(Integer id);
    
    /**
     * 查询比赛的所有对阵（包含选手姓名等详细信息）
     */
    List<MatchVO> selectByCompetitionAndGroup(@Param("competitionId") Integer competitionId, 
                                              @Param("groupLevel") Integer groupLevel);
    
    /**
     * 查询指定轮次的对阵
     */
    List<Match> selectByRound(@Param("competitionId") Integer competitionId, 
                              @Param("groupLevel") Integer groupLevel, 
                              @Param("roundNumber") Integer roundNumber);
    
    /**
     * 更新比赛结果
     */
    int updateResult(@Param("id") Integer id, 
                     @Param("winnerId") Integer winnerId, 
                     @Param("score") String score);
    
    /**
     * 获取最大轮次
     */
    Integer selectMaxRound(@Param("competitionId") Integer competitionId, 
                           @Param("groupLevel") Integer groupLevel);
    
    /**
     * 统计指定轮次已完成的比赛数量
     */
    Integer countCompletedMatches(@Param("competitionId") Integer competitionId, 
                                  @Param("groupLevel") Integer groupLevel, 
                                  @Param("roundNumber") Integer roundNumber);
    
    /**
     * 统计指定轮次的总比赛数量
     */
    Integer countTotalMatches(@Param("competitionId") Integer competitionId, 
                              @Param("groupLevel") Integer groupLevel, 
                              @Param("roundNumber") Integer roundNumber);
    
    /**
     * 删除比赛的所有对阵记录
     */
    int deleteByCompetition(@Param("competitionId") Integer competitionId, 
                            @Param("groupLevel") Integer groupLevel);
    /**
     * 按赛事+组别筛选，返回每组胜场最多2人的最后一场比赛（兼容所有MySQL版本）
     * @param competitionId 赛事ID
     * @param groupLevel 组别（1-甲，2-乙，3-丙）
     * @return 每组TOP2选手的最后一场比赛列表
     */
    @Select("WITH match_wins AS (" +
            "    SELECT " +
            "        m.*, " +
            "        CASE WHEN m.winner_id = m.player1_id THEN m.player1_id " +
            "             WHEN m.winner_id = m.player2_id THEN m.player2_id " +
            "        END AS winner_player_id, " +
            "        COUNT(CASE WHEN m.winner_id = m.player1_id THEN 1 END) OVER (PARTITION BY m.player1_id) AS player1_win_count, " +
            "        COUNT(CASE WHEN m.winner_id = m.player2_id THEN 1 END) OVER (PARTITION BY m.player2_id) AS player2_win_count " +
            "    FROM matches m " +
            "    WHERE m.competition_id = #{competitionId} " +
            "      AND m.group_level = #{groupLevel} " +
            "), " +
            "player_win_counts AS (" +
            "    SELECT " +
            "        DISTINCT " +
            "        CASE WHEN pw.winner_player_id = pw.player1_id THEN pw.player1_id " +
            "             WHEN pw.winner_player_id = pw.player2_id THEN pw.player2_id " +
            "        END AS player_id, " +
            "        pw.group_number, " +
            "        CASE WHEN pw.winner_player_id = pw.player1_id THEN pw.player1_win_count " +
            "             WHEN pw.winner_player_id = pw.player2_id THEN pw.player2_win_count " +
            "        END AS win_count " +
            "    FROM match_wins pw " +
            "), " +
            "top_players AS (" +
            "    SELECT " +
            "        p.player_id, " +
            "        p.group_number, " +
            "        p.win_count, " +
            "        ROW_NUMBER() OVER (PARTITION BY p.group_number ORDER BY p.win_count DESC) AS rn " +
            "    FROM player_win_counts p " +
            "), " +
            "final_players AS (" +
            "    SELECT tp.player_id, tp.group_number " +
            "    FROM top_players tp " +
            "    WHERE tp.rn <= 2 " +
            "), " +
            "last_wins AS (" +
            "    SELECT " +
            "        m.*, " +
            "        ROW_NUMBER() OVER (PARTITION BY m.winner_id ORDER BY m.id DESC) AS win_order " +
            "    FROM matches m " +
            "    INNER JOIN final_players fp ON m.winner_id = fp.player_id " +
            "    WHERE m.competition_id = #{competitionId} " +
            "      AND m.group_level = #{groupLevel} " +
            ") " +
            "SELECT lw.* " +
            "FROM last_wins lw " +
            "WHERE lw.win_order = 1")
    List<Match> selectByGroup(
        @Param("competitionId") Integer competitionId,
        @Param("groupLevel") Integer groupLevel
    );
}
